const mysql = require('mysql2/promise')

async function initTables () {
  try {
    console.log('🔍 连接到数据库...')

    // 连接到数据库
    const connection = await mysql.createConnection({
      host: 'localhost',
      port: 3306,
      user: 'root',
      password: 'GKL9512357',
      database: 'novel_platform'
    })

    console.log('✅ 数据库连接成功')

    // 直接定义所有CREATE TABLE语句
    const createTableStatements = [
      `CREATE TABLE IF NOT EXISTS users (
        id INT PRIMARY KEY AUTO_INCREMENT,
        username VARCHAR(50) UNIQUE NOT NULL,
        email VARCHAR(100) UNIQUE NOT NULL,
        password_hash VARCHAR(255) NOT NULL,
        nickname VARCHAR(50),
        avatar_url VARCHAR(255),
        gender ENUM('male', 'female', 'other') DEFAULT 'other',
        birth_date DATE,
        phone VARCHAR(20),
        bio TEXT,
        reading_preferences JSON,
        is_vip BOOLEAN DEFAULT FALSE,
        vip_expire_date DATETIME,
        coins INT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      )`,

      `CREATE TABLE IF NOT EXISTS authors (
        id INT PRIMARY KEY AUTO_INCREMENT,
        user_id INT UNIQUE NOT NULL,
        pen_name VARCHAR(100) NOT NULL,
        real_name VARCHAR(50),
        bio TEXT,
        total_works INT DEFAULT 0,
        total_words BIGINT DEFAULT 0,
        total_income DECIMAL(10, 2) DEFAULT 0.00,
        status ENUM('active', 'suspended', 'inactive') DEFAULT 'active',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
      )`,

      `CREATE TABLE IF NOT EXISTS novels (
        id INT PRIMARY KEY AUTO_INCREMENT,
        title VARCHAR(200) NOT NULL,
        author_id INT NOT NULL,
        description TEXT,
        cover_url VARCHAR(255),
        category VARCHAR(50) NOT NULL,
        tags JSON,
        status ENUM('ongoing', 'completed', 'suspended') DEFAULT 'ongoing',
        word_count BIGINT DEFAULT 0,
        chapter_count INT DEFAULT 0,
        view_count BIGINT DEFAULT 0,
        favorite_count INT DEFAULT 0,
        rating DECIMAL(3, 2) DEFAULT 0.00,
        rating_count INT DEFAULT 0,
        is_vip BOOLEAN DEFAULT FALSE,
        price_per_chapter DECIMAL(5, 2) DEFAULT 0.00,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE CASCADE,
        INDEX idx_category (category),
        INDEX idx_status (status),
        INDEX idx_created_at (created_at),
        INDEX idx_view_count (view_count),
        INDEX idx_rating (rating)
      )`,

      `CREATE TABLE IF NOT EXISTS chapters (
        id INT PRIMARY KEY AUTO_INCREMENT,
        novel_id INT NOT NULL,
        title VARCHAR(200) NOT NULL,
        content LONGTEXT NOT NULL,
        chapter_number INT NOT NULL,
        word_count INT DEFAULT 0,
        is_vip BOOLEAN DEFAULT FALSE,
        price DECIMAL(5, 2) DEFAULT 0.00,
        view_count BIGINT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (novel_id) REFERENCES novels(id) ON DELETE CASCADE,
        UNIQUE KEY unique_novel_chapter (novel_id, chapter_number),
        INDEX idx_novel_id (novel_id),
        INDEX idx_chapter_number (chapter_number)
      )`,

      `CREATE TABLE IF NOT EXISTS user_favorites (
        id INT PRIMARY KEY AUTO_INCREMENT,
        user_id INT NOT NULL,
        novel_id INT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (novel_id) REFERENCES novels(id) ON DELETE CASCADE,
        UNIQUE KEY unique_user_novel (user_id, novel_id)
      )`,

      `CREATE TABLE IF NOT EXISTS reading_history (
        id INT PRIMARY KEY AUTO_INCREMENT,
        user_id INT NOT NULL,
        novel_id INT NOT NULL,
        chapter_id INT NOT NULL,
        progress DECIMAL(5, 2) DEFAULT 0.00,
        last_read_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (novel_id) REFERENCES novels(id) ON DELETE CASCADE,
        FOREIGN KEY (chapter_id) REFERENCES chapters(id) ON DELETE CASCADE,
        UNIQUE KEY unique_user_novel (user_id, novel_id),
        INDEX idx_user_id (user_id),
        INDEX idx_last_read_at (last_read_at)
      )`,

      `CREATE TABLE IF NOT EXISTS comments (
        id INT PRIMARY KEY AUTO_INCREMENT,
        user_id INT NOT NULL,
        novel_id INT NOT NULL,
        chapter_id INT,
        content TEXT NOT NULL,
        rating INT CHECK (rating >= 1 AND rating <= 5),
        like_count INT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (novel_id) REFERENCES novels(id) ON DELETE CASCADE,
        FOREIGN KEY (chapter_id) REFERENCES chapters(id) ON DELETE CASCADE,
        INDEX idx_novel_id (novel_id),
        INDEX idx_user_id (user_id),
        INDEX idx_created_at (created_at)
      )`,

      `CREATE TABLE IF NOT EXISTS user_wallets (
        id INT PRIMARY KEY AUTO_INCREMENT,
        user_id INT UNIQUE NOT NULL,
        balance DECIMAL(10, 2) DEFAULT 0.00,
        total_recharge DECIMAL(10, 2) DEFAULT 0.00,
        total_consumption DECIMAL(10, 2) DEFAULT 0.00,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
      )`
    ]

    console.log(`📝 准备创建 ${createTableStatements.length} 个表...`)

    // 逐条执行SQL语句
    for (let i = 0; i < createTableStatements.length; i++) {
      const statement = createTableStatements[i]
      try {
        await connection.query(statement)
        console.log(`✅ 表 ${i + 1}/${createTableStatements.length} 创建成功`)
      } catch (error) {
        if (error.code === 'ER_TABLE_EXISTS_ERROR') {
          console.log(`⚠️  表 ${i + 1}/${createTableStatements.length} 已存在`)
        } else {
          console.error(
            `❌ 表 ${i + 1}/${createTableStatements.length} 创建失败:`,
            error.message
          )
        }
      }
    }

    console.log('🎉 数据库表初始化完成')

    // 检查表是否创建成功
    const [tables] = await connection.query('SHOW TABLES')
    console.log(
      '📊 数据库中的表:',
      tables.map(t => Object.values(t)[0])
    )

    // 关闭连接
    await connection.end()
    console.log('🔌 连接已关闭')
  } catch (error) {
    console.error('❌ 初始化数据库表失败:', error.message)
  }
}

initTables()
